package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.ControlCarDto;
import com.tianqu.tms.dto.TransferFinanceInfoDto;
import com.tianqu.tms.entity.Codes;
import com.tianqu.tms.entity.TransferFinanceInfo;

public class TransferFinanceInfoDaoImp extends TmsDao implements
		TransferFinanceInfoDao {

	protected TransferFinanceInfoDaoImp() throws TmsDaoException {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * 评估
	 */
	@Override
	public void updateFinanceInfoAtAssess(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" ASSESSPRICE=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getAssessPrice());
			ps.setString(2, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
		
	}
	/**
	 * 在打印流程表的时候向表中插入数据
	 * */
	@Override
	public void insertProcedure(TransferFinanceInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer(" INSERT INTO  ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append("(");
		sql.append(" serial, ");
		sql.append(" checkprice, ");			
		sql.append(" checktype, ");
		sql.append(" checktime");
		sql.append("  ) VALUES ( ");
		sql.append(" ?,?,?,SYSDATE())");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getCheckPrice());
			//ps.setInt(3, dto.getChecktype());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
	}
	
	/*
	 * 判断是否存在相同流水号的车辆
	 * */
	
	@Override
	public boolean insertProcedureFlag(String serial) throws TmsDaoException {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = true;
		StringBuffer sql = new StringBuffer(" SELECT SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL=?");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs=ps.executeQuery();
			while(rs.next()){
				flag = false;
			}
			
		}  catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		return flag;
		
	}

	/**
	 * 查验
	 */
	@Override
	public void updateFinanceInfoAtCheck(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" checkPrice=?,   ");
			sql.append(" transferPrice=?,   ");
			sql.append(" checkPriceUserId=?,   ");
			sql.append(" transferPriceUserId=?,   ");
			sql.append(" checkPriceChargeType=?,   ");
			sql.append(" transferPriceChargeType=?,   ");
			sql.append(" checkPriceDate=SYSDATE(),   ");
			sql.append(" transferPriceDate=SYSDATE()   ");
			sql.append(" WHERE SERIAL=?  ");
			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getCheckPrice());
			ps.setInt(2, dto.getTransferPrice());
			ps.setInt(3, dto.getCheckPriceUserId());
			ps.setInt(4, dto.getTransferPriceUserId());
			ps.setInt(5, dto.getCheckPriceChargeType());
			ps.setInt(6, dto.getTransferPriceChargeType());
			ps.setString(7, dto.getSerial());

			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
	}
	@Override
	public void updateFinanceInfoAtMarket(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" CHECKPRICE=?,   ");
			if(dto.getTransferPriceFlag() ==2){//
				sql.append("TRANSFERPRICE=?,");
				sql.append("TRANSFERPRICEDATE=SYSDATE(),");
				sql.append("TRANSFERPRICEUSERID=?,");
			}
			sql.append("CHECKPRICEUSERID=?,");
			sql.append(" CHECKPRICEDATE=SYSDATE(),");
			sql.append(" CHECKPRICECHARGETYPE=?,");
			sql.append(" TRANSFERPRICECHARGETYPE=? ");

			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getCheckPrice());
			if(dto.getTransferPriceFlag() ==2){
				ps.setInt(2, dto.getTransferPrice());
				ps.setInt(3, dto.getTransferPriceUserId());
				ps.setInt(4, dto.getCheckPriceUserId());
				ps.setInt(5, dto.getCheckPriceChargeType());
				ps.setInt(6, dto.getTransferPriceChargeType());
				ps.setString(7, dto.getSerial());
			}else{
				ps.setInt(2, dto.getCheckPriceUserId());
				ps.setInt(3, dto.getCheckPriceChargeType());
				ps.setInt(4, dto.getTransferPriceChargeType());
				ps.setString(5, dto.getSerial());
			}
			
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
	}
	@Override
	public TransferFinanceInfoDto findFinanceInfoByserial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("CHECKPRICE, ");
		sql.append("ASSESSPRICE, ");
		sql.append("AGENCYPRICE,    ");
		sql.append("EXTENDEDPRICE,    ");
		sql.append("TRANSFERPRICE,    ");
		sql.append("OTHERBACKPRICE,    ");
		sql.append("SDBACKPRICE,    ");
		sql.append("CHANGEPRICE,    ");
		sql.append("PZBACKPRICE, ");
		sql.append("ID, ");
		sql.append("TRANSFERPRICEDATE, ");
		sql.append("CHECKPRICEDATE, ");
		sql.append("AGENCYPRICEDATE, ");
		sql.append("CHANGEPRICEDATE, ");
		sql.append("CHECKPRICECHARGETYPE,  ");
		sql.append("TRANSFERPRICECHARGETYPE  ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append("   WHERE SERIAL ='" + serial + "' ");

		TransferFinanceInfoDto dto = null;
		try {

			ps = conn.prepareStatement(sql.toString());

			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new TransferFinanceInfoDto();
			 dto.setCheckPrice(rs.getInt("CHECKPRICE"));
			 dto.setAssessPrice(rs.getInt("ASSESSPRICE"));
			 dto.setAgencyPrice(rs.getInt("AGENCYPRICE"));
			 dto.setExtendedPrice(rs.getInt("EXTENDEDPRICE"));
			 dto.setTransferPrice(rs.getInt("TRANSFERPRICE"));
			 dto.setOtherBackPrice(rs.getInt("OTHERBACKPRICE"));
			 dto.setSdBackPrice(rs.getInt("SDBACKPRICE"));
			 dto.setChangePrice(rs.getInt("CHANGEPRICE"));
			 dto.setPzBackPrice(rs.getInt("PZBACKPRICE")); 
			 dto.setId(rs.getInt("ID")); 
			 dto.setTransferPriceDate(rs.getTimestamp("TRANSFERPRICEDATE"));
			 dto.setCheckPriceDate(rs.getTimestamp("CHECKPRICEDATE"));
			 dto.setAgencyPriceDate(rs.getTimestamp("AGENCYPRICEDATE"));
			 dto.setChangePriceDate(rs.getTimestamp("CHANGEPRICEDATE")); 
			 dto.setCheckPriceChargeType(rs.getInt("CHECKPRICECHARGETYPE"));
			 dto.setTransferPriceChargeType(rs.getInt("TRANSFERPRICECHARGETYPE")); 
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	@Override
	public void updateFinanceInfoPrice(TransferFinanceInfoDto dto) throws TmsDaoException {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		ResultSet rs = null;	
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" CHECKPRICE=? ,   ");
			sql.append(" ASSESSPRICE=? ,  ");
			sql.append(" AGENCYPRICE=? ,  ");
			sql.append(" EXTENDEDPRICE=? ,  ");
			sql.append(" TRANSFERPRICE=? ,  ");
			sql.append(" OTHERBACKPRICE=? ,  ");
			sql.append(" SDBACKPRICE=? ,  ");
			sql.append(" CHANGEPRICE=?,  ");
			sql.append(" PZBACKPRICE=?,  ");
			sql.append(" BACKCHARGETYPE=?,  ");
			sql.append(" AGENCYCHARGETYPE=?  ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getCheckPrice());
			ps.setInt(2, dto.getAssessPrice());
			ps.setInt(3, dto.getAgencyPrice());
			ps.setInt(4, dto.getExtendedPrice());
			ps.setInt(5, dto.getTransferPrice());
			ps.setInt(6, dto.getOtherBackPrice());
			ps.setInt(7, dto.getSdBackPrice());
			ps.setInt(8, dto.getChangePrice());
			ps.setInt(9,dto.getPzBackPrice());
			ps.setInt(10, dto.getBackChargeType());
			ps.setInt(11, dto.getAgencyChargeType());
			ps.setString(12, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
		
	}

	@Override
	public void insertFinanceByInner(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer(" INSERT INTO  ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append("(");
		sql.append(" SERIAL ");		
		sql.append("  ) VALUES ( ");
		sql.append(" ?");
		sql.append(" )");
		try {
			ps = conn.prepareStatement(sql.toString());			
			ps.setString(1, serial);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
	}

	@Override
	public void updateFinanceInfoAtReback(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" SERIAL=?    ");
			sql.append(" WHERE ID=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getId());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}							
	}


	@Override
	public void deleteFinanceInfoBySerial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append("DELETE ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" WHERE ");
			sql.append(" SERIAL='"+serial+"' ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}			
	}

	@Override
	public void updateTransferTime(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" TRANSFERPRICEDATE=SYSDATE(),   ");
			sql.append(" TRANSFERPRICEUSERID=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getTransferPriceUserId());
			ps.setString(2, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	@Override
	public void updateTransferCheckBackPrice(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" CHECKBACKPRICE=?,   ");
			sql.append(" CHECKBACKPRICEDATE=SYSDATE(),   ");
			sql.append(" CHECKBACKPRICEUSERID=?,   ");
			sql.append(" CHECKBACKCHARGETYPE=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getCheckBackPrice());
			ps.setInt(2, dto.getCheckBackPriceUserId());
			ps.setInt(3, dto.getCheckBackChargeType());
			ps.setString(4, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	@Override
	public void updateChangePriceDate(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" CHANGEPRICEDATE=SYSDATE(),   ");
			sql.append(" CHANGEPRICEUSERID=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getChangePriceUserId());
			ps.setString(2, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	@Override
	public void updateBackPriceDate(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" BACKPRICEDATE=SYSDATE(),   ");
			sql.append(" BACKPRICEUSERID=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getBackPriceUserId());
			ps.setString(2, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
	}

	@Override
	public void updateAgencyPriceDate(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" AGENCYPRICEDATE=SYSDATE(),   ");
			sql.append(" AGENCYPRICEUSERID=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getAgencyPriceUserId());
			ps.setString(2, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	@Override
	public void updateExtendedPriceDate(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" EXTENDEDPRICEDATE=SYSDATE(),   ");
			sql.append(" EXTENDEDPRICEUSERID=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getExtendedPriceUserId());
			ps.setString(2, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	@Override
	public List getChargeInfoByPeriod(String startDate, String endDate)
			throws TmsDaoException {
		List list = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" MARKET.ID AS ID,");
		sql.append(" MARKET.SERIAL AS SERIAL,");
		sql.append(" MARKET.LICENSEPLATE AS LICENSEPLATE,");
		sql.append(" MEMBER.REALNAME AS REALNAME,");
		sql.append(" TRANSFER.SERIAL AS TRANSFERSERIAL,");
		sql.append(" MARKET.VIN AS VIN,");
		sql.append(" MARKET.INSERTTIME AS INSERTTIME, ");
		sql.append(" MARKET.CARSCORE AS CARSCORE");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);

		
		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			ControlCarDto controlCarDto = null;
			int i=0;
			while (rs.next()) {
				controlCarDto = new ControlCarDto();
				controlCarDto.setId(rs.getLong("ID"));
				controlCarDto.setSerial(rs.getString("SERIAL")); 
				controlCarDto.setCarLicense(rs.getString("LICENSEPLATE"));
				controlCarDto.setMemberName(rs.getString("REALNAME"));
				controlCarDto.setInsertTime(DateUtil.getTimeByTimeStamp1(rs.getTimestamp("INSERTTIME")));	
				if(i%2 == 0){
					controlCarDto.setCssFlag(1);
				}else{
					controlCarDto.setCssFlag(2);
				}
				controlCarDto.setTransferSerial(rs.getString("TRANSFERSERIAL"));
				controlCarDto.setVin(rs.getString("VIN"));
				controlCarDto.setCarScore(rs.getInt("CARSCORE"));
				list.add(controlCarDto);
			}
			return list;
			
		} 
		
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodLC(String startDate,
			String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" SUM(CHECKPRICE) AS CHECKPRICE,		");
		sql.append(" CHECKPRICEUSERID,		");
		sql.append(" COUNT(*) AS COUNT		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(CHECKPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(CHECKPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND CHECKPRICEUSERID="+userid+" ");
		sql.append(" AND CHECKPRICECHARGETYPE<>2 ");
		sql.append(" GROUP BY CHECKPRICEUSERID ");

		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setCheckPrice(rs.getInt("CHECKPRICE"));
				financeDto.setCheckPriceUserId(rs.getInt("CHECKPRICEUSERID")); 
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodJYP(
			String startDate, String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(TRANSFERPRICE) AS TRANSFERPRICE,		");
		sql.append(" COUNT(*) AS COUNT,		");
		sql.append(" TRANSFERPRICEUSERID		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(TRANSFERPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(TRANSFERPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND TRANSFERPRICEUSERID="+userid+" ");
		sql.append(" AND TRANSFERPRICECHARGETYPE<>2  ");
		sql.append(" GROUP BY  TRANSFERPRICEUSERID		");

		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setTransferPrice(rs.getInt("TRANSFERPRICE"));
				financeDto.setTransferPriceUserId(rs.getInt("TRANSFERPRICEUSERID"));
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodDB(
			String startDate, String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(AGENCYPRICE) AS AGENCYPRICE,		");
		sql.append(" COUNT(*) AS COUNT,		");
		sql.append(" AGENCYPRICEUSERID		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(AGENCYPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(AGENCYPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND AGENCYPRICEUSERID="+userid+" ");
		sql.append(" AND AGENCYCHARGETYPE<>2  ");
		sql.append(" GROUP BY  AGENCYPRICEUSERID		");

		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setAgencyPrice(rs.getInt("AGENCYPRICE"));
				financeDto.setAgencyPriceUserId(rs.getInt("AGENCYPRICEUSERID"));
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodDBOut(
			String startDate, String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(EXTENDEDPRICE) AS EXTENDEDPRICE,		");
		sql.append(" COUNT(*) AS COUNT,		");
		sql.append(" EXTENDEDPRICEUSERID		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(EXTENDEDPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(EXTENDEDPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND EXTENDEDPRICEUSERID="+userid+" ");
		sql.append(" GROUP BY EXTENDEDPRICEUSERID		");

		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setExtendedPrice(rs.getInt("EXTENDEDPRICE"));
				financeDto.setExtendedPriceUserId(rs.getInt("EXTENDEDPRICEUSERID"));
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodBG(
			String startDate, String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(CHANGEPRICE) AS CHANGEPRICE,		");
		sql.append(" COUNT(*) AS COUNT,		");
		sql.append(" CHANGEPRICEUSERID		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(CHANGEPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(CHANGEPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND CHANGEPRICEUSERID="+userid+" ");
		sql.append(" GROUP BY CHANGEPRICEUSERID		");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setChangePrice(rs.getInt("CHANGEPRICE"));
				financeDto.setChangePriceUserId(rs.getInt("CHANGEPRICEUSERID"));
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodLCT(
			String startDate, String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(CHECKBACKPRICE) AS CHECKBACKPRICE,		");
		sql.append(" COUNT(*) AS COUNT,		");
		sql.append(" CHECKBACKPRICEUSERID		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(CHECKBACKPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(CHECKBACKPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND CHECKBACKPRICEUSERID="+userid+" ");
		sql.append(" AND CHECKBACKCHARGETYPE<>2 ");
		sql.append(" GROUP BY  CHECKBACKPRICEUSERID		");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setCheckBackPrice(rs.getInt("CHECKBACKPRICE"));
				financeDto.setCheckBackPriceUserId(rs.getInt("CHECKBACKPRICEUSERID"));
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferFinanceInfoDto> getFinaneInfoByPeriodJYPT(
			String startDate, String endDate,int userid) throws TmsDaoException {
		List<TransferFinanceInfoDto> list = new ArrayList<TransferFinanceInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(OTHERBACKPRICE) AS OTHERBACKPRICE,		");
		sql.append(" SUM(SDBACKPRICE) AS SDBACKPRICE,		");
		sql.append(" SUM(PZBACKPRICE) AS PZBACKPRICE,		");
		sql.append(" COUNT(*) AS COUNT,		");
		sql.append(" BACKPRICEUSERID		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" WHERE DATE_FORMAT(BACKPRICEDATE,'%Y-%m-%d %H:%i')>'"+startDate+"' AND DATE_FORMAT(BACKPRICEDATE,'%Y-%m-%d %H:%i')<='"+endDate+"' ");
		sql.append(" AND BACKPRICEUSERID="+userid+" ");
		sql.append(" AND BACKCHARGETYPE<>2  ");
		sql.append(" GROUP BY BACKPRICEUSERID		");

		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferFinanceInfoDto financeDto = null;
			while (rs.next()) {
				financeDto = new TransferFinanceInfoDto();
				financeDto.setOtherBackPrice(rs.getInt("OTHERBACKPRICE"));
				financeDto.setSdBackPrice(rs.getInt("SDBACKPRICE"));
				financeDto.setPzBackPrice(rs.getInt("PZBACKPRICE"));
				financeDto.setBackPriceUserId(rs.getInt("BACKPRICEUSERID"));
				financeDto.setCount(rs.getInt("COUNT")); 
				list.add(financeDto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}
	
	@Override
	public void insertTransferFinanceInfo(TransferFinanceInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer(" INSERT INTO  ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append("(");
		sql.append("checkPrice             ,");
		sql.append("assessPrice            ,");
		sql.append("agencyPrice            ,");
		sql.append("extendedPrice          ,");
		sql.append("transferPrice          ,");
		sql.append("otherBackPrice         ,");
		sql.append("sdBackPrice            ,");
		sql.append("changePrice            ,");
		sql.append("pzBackPrice            ,");
		sql.append("checkPriceDate         ,");
		sql.append("transferPriceDate      ,");
		sql.append("changePriceDate        ,");
		sql.append("backPriceDate          ,");
		sql.append("checkBackPrice         ,");
		sql.append("checkBackPriceDate     ,");
		sql.append("agencyPriceDate        ,");
		sql.append("extendedPriceDate      ,");
		sql.append("checkPriceUserId       ,");
		sql.append("agencyPriceUserId      ,");
		sql.append("extendedPriceUserId    ,");
		sql.append("transferPriceUserId    ,");
		sql.append("backPriceUserId        ,");
		sql.append("changePriceUserId      ,");
		sql.append("checkBackPriceUserId   ,");
		sql.append("checkPriceChargeType   ,");
		sql.append("transferPriceChargeType,");
		sql.append("backChargeType         ,");
		sql.append("checkBackChargeType    ,");
		sql.append("agencyChargeType       ");
		sql.append("  ) VALUES ( ");
		sql.append(" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getCheckPrice());
			ps.setInt(3, dto.getAssessPrice());
			ps.setInt(4, dto.getAgencyPrice());
			ps.setInt(5, dto.getExtendedPrice());
			ps.setInt(6, dto.getTransferPrice());
			ps.setInt(7, dto.getOtherBackPrice());
			ps.setInt(8, dto.getSdBackPrice());
			ps.setInt(9, dto.getChangePrice());
			ps.setInt(10, dto.getPzBackPrice());	
			ps.setTimestamp(11, dto.getCheckPriceDate());
			ps.setTimestamp(12, dto.getTransferPriceDate());
			ps.setTimestamp(13, dto.getChangePriceDate());
			ps.setTimestamp(14, dto.getBackPriceDate());
			ps.setInt(15, dto.getCheckBackPrice());
			ps.setTimestamp(16, dto.getCheckBackPriceDate());
			ps.setTimestamp(17, dto.getAgencyPriceDate());
			ps.setTimestamp(18, dto.getExtendedPriceDate());
			ps.setInt(19, dto.getCheckPriceUserId());
			ps.setInt(20, dto.getAgencyPriceUserId());
			ps.setInt(21, dto.getExtendedPriceUserId());
			ps.setInt(22, dto.getTransferPriceUserId());
			ps.setInt(23, dto.getBackPriceUserId());
			ps.setInt(24, dto.getChangePriceUserId());
			ps.setInt(25, dto.getCheckBackPriceUserId());
			ps.setInt(26, dto.getCheckBackChargeType());
			ps.setInt(27, dto.getTransferPriceChargeType());
			ps.setInt(28, dto.getBackChargeType());
			ps.setInt(29, dto.getCheckBackChargeType());
			ps.setInt(30, dto.getAgencyChargeType());			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
	}

	@Override
	public void updateTransferDataSyn40000(TransferFinanceInfoDto dto)
			throws TmsDaoException {
		int i = 1;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE  ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append(" SET  ");
		sql.append("checkPrice=?,");
		sql.append("checkPriceDate=SYSDATE(),");
		sql.append("checkPriceUserId = 1,");
		sql.append("checkPriceChargeType = ?,");
		if(dto.getBusinessType() == 1) {
			sql.append("transferPrice=?,");
			sql.append("transferPriceDate=SYSDATE(),");
			sql.append("transferPriceUserId = 1,");
			sql.append("transferPriceChargeType = ?");
		}
		sql.append(" WHERE serial =?  ");//
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(i++, dto.getCheckPrice());
			ps.setInt(i++, dto.getCheckPriceChargeType());
			if(dto.getBusinessType() == 1) {
				ps.setInt(i++, dto.getTransferPrice());
				ps.setInt(i++, dto.getTransferPriceChargeType());
			}
			ps.setString(i++, dto.getSerial());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}		
	}
	public void insertFinanceInfo(TransferFinanceInfoDto dto,int type,int checkPriceType) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer(" INSERT INTO  ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
		sql.append("(");
		sql.append(" SERIAL ");
		sql.append(" ,CHECKPRICE ");
		sql.append(" ,CHECKPRICECHARGETYPE ");		
		sql.append(" ,CHECKPRICEUSERID ");
		if(checkPriceType == 1){
			sql.append(" ,CHECKPRICEDATE ");
		}
		if(type == 1){
			sql.append(" ,TRANSFERPRICE ");
			sql.append(" ,TRANSFERPRICECHARGETYPE ");		
			sql.append(" ,TRANSFERPRICEUSERID ");
			sql.append(" ,TRANSFERPRICEDATE ");
		}
		sql.append("  ) VALUES ( ");
		if(type == 1 && checkPriceType == 1){
			sql.append(" ?,?,?,?,SYSDATE(),?,?,?,SYSDATE() )");
		}else if(type != 1 && checkPriceType == 1) {
			sql.append(" ?,?,?,?,SYSDATE() )");
		}else if(type == 1 && checkPriceType != 1){
			sql.append(" ?,?,?,?,?,?,?,SYSDATE() )");
		}else if(type != 1 && checkPriceType != 1){
			sql.append(" ?,?,?,? )");
		}
		try {
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getCheckPrice());
			ps.setInt(3, dto.getCheckPriceChargeType());
			ps.setInt(4, dto.getCheckPriceUserId());
			if(type == 1){
				ps.setInt(5, dto.getTransferPrice());
				ps.setInt(6, dto.getTransferPriceChargeType());
				ps.setInt(7, dto.getTransferPriceUserId());
			}

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	
	@Override
	public void updateFinanceInfoAtWXByCheckPrice(TransferFinanceInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" CHECKPRICE=?,   ");
			sql.append(" CHECKPRICEDATE=SYSDATE(),   ");
			sql.append(" CHECKPRICEUSERID=?,   ");
			sql.append(" CHECKPRICECHARGETYPE=?   ");
			sql.append(" WHERE SERIAL=?  ");

			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getCheckPrice());
			ps.setInt(2, dto.getCheckPriceUserId());
			ps.setInt(3, dto.getCheckPriceChargeType());
			ps.setString(4, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
		
	}
	
	@Override
	public void updateFinanceInfoAtWXByTransferPrice(TransferFinanceInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_INFO);
			sql.append(" SET  ");
			sql.append(" TRANSFERPRICE=?,   ");
			sql.append(" TRANSFERPRICEDATE=SYSDATE(),   ");
			sql.append(" TRANSFERPRICEUSERID=?,   ");
			sql.append(" TRANSFERPRICECHARGETYPE=?   ");
			sql.append(" WHERE SERIAL=?  ");
			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getTransferPrice());
			ps.setInt(2, dto.getTransferPriceUserId());
			ps.setInt(3, dto.getTransferPriceChargeType());
			ps.setString(4, dto.getSerial());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
		
	}
	
	public static void main(String[] s) {
		String dateFrom = "20180605";
		String dateTo = "20180605";
		String priceType = "2";
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" A.total,");
		sql.append(" A.priceType,");
		sql.append(" A.price ");
		sql.append(" FROM ");
		sql.append("( select ");
		sql.append(" count(*) total,");
		sql.append(" AA.priceType priceType,");
		sql.append(" sum(AA.price) price ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql.append(" WHERE  AA.serial=BB.serial and BB.status in (0,16) and  AA.priceType= 9 ");
		sql.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql.append(" group by AA.priceType");
		sql.append("  union all");
		sql.append(" select ");
		sql.append(" count(*) total,");
		sql.append(" AA.priceType priceType,");
		sql.append(" sum(AA.price) price ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql.append(" WHERE  AA.serial=BB.serial and  AA.priceType<> 9 ");
		sql.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql.append(" group by AA.priceType ) A");
		sql.append(" order by A.total desc ,A.price desc");
		System.out.println(sql.toString());
		
		StringBuffer sql0 = new StringBuffer();
		sql0.append(" select ");
		sql0.append(" AA.serial serial,");
		sql0.append(" BB.attnId attnId,");
		sql0.append(" BB.transferType transferType, ");
		sql0.append(" AA.priceType priceType,");
		sql0.append(" AA.price price,");
		sql0.append(" AA.acceptedTime acceptedTime,");
		sql0.append(" AA.priceUserId priceUserId,");
		sql0.append(" AA.priceDate priceDate");
		sql0.append(" FROM ");
		sql0.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql0.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql0.append(" WHERE  AA.serial=BB.serial and  AA.priceType=9 and  BB.status not in (0,16) ");
		sql0.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql0.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql0.append(" order by AA.serial  ");
		System.out.println(sql0.toString());
		
		StringBuffer sql1 = new StringBuffer();
		sql1.append(" select ");
		sql1.append(" A.serial serial ,");
		sql1.append(" A.attnId attnId,");
		sql1.append(" A.transferType transferType, ");
		sql1.append(" A.priceType priceType,");
		sql1.append(" A.price price,");
		sql1.append(" A.acceptedTime acceptedTime,");
		sql1.append(" A.priceUserId priceUserId,");
		sql1.append(" A.priceDate priceDate");
	    sql1.append(" FROM ");
		sql1.append(" (select ");
		sql1.append(" AA.serial ,");
		sql1.append(" BB.attnId ,");
		sql1.append(" BB.transferType, ");
		sql1.append(" AA.priceType ,");
		sql1.append(" AA.price ,");
		sql1.append(" AA.acceptedTime ,");
		sql1.append(" AA.priceUserId ,");
		sql1.append(" AA.priceDate ");
		sql1.append(" FROM ");
		sql1.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql1.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql1.append(" WHERE  AA.serial=BB.serial and BB.status in (0,16) and  AA.priceType= 9 ");
		sql1.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql1.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql1.append("  union ");
		sql1.append(" select ");
		sql1.append(" AA.serial ,");
		sql1.append(" BB.attnId ,");
		sql1.append(" BB.transferType, ");
		sql1.append(" AA.priceType ,");
		sql1.append(" AA.price ,");
		sql1.append(" AA.acceptedTime ,");
		sql1.append(" AA.priceUserId ,");
		sql1.append(" AA.priceDate ");
		sql1.append(" FROM ");
		sql1.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql1.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql1.append(" WHERE  AA.serial=BB.serial and  AA.priceType<> 9 ");
		sql1.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql1.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql1.append(" ) A  where A.priceType = "+priceType);
		sql1.append(" order by A.serial  ");
		System.out.println(sql1.toString());
		
	}
	
	@Override
	public List<TransferFinanceInfo> findTransferFinanceList(String dateFrom,String dateTo) throws TmsDaoException {
		List<TransferFinanceInfo> list = new ArrayList<TransferFinanceInfo>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" A.total,");
		sql.append(" A.priceType,");
		sql.append(" A.price ");
		sql.append(" FROM ");
		sql.append("( select ");
		sql.append(" count(*) total,");
		sql.append(" AA.priceType priceType,");
		sql.append(" sum(AA.price* CASE AA.priceWay WHEN 2 THEN -1 ELSE 1 END) price ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql.append(" WHERE  AA.serial=BB.serial and BB.status in (0,16) and  AA.priceType= 9 ");
		sql.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql.append(" group by AA.priceType");
		sql.append("  union all");
		sql.append(" select ");
		sql.append(" count(*) total,");
		sql.append(" AA.priceType priceType,");
		sql.append(" sum(AA.price* CASE AA.priceWay WHEN 2 THEN -1 ELSE 1 END) price ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql.append(" WHERE  AA.serial=BB.serial and  AA.priceType<> 9 ");
		sql.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql.append(" group by AA.priceType ) A");
		sql.append(" order by A.total desc ,A.price desc");
		try {
			Codes code = Codes.getInstance();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				TransferFinanceInfo info = new TransferFinanceInfo();
				info.setTotal(rs.getString("total"));
				info.setPrice(rs.getString("price"));
				int priceType = rs.getInt("priceType");
				info.setPriceType(priceType);
				info.setPriceTypeStr(code.getItemValueByItemIdAndItemCode(Codes.CHARGE_TYPE_CHARGE, priceType));
				if(StringUtil.isEmpty(info.getPriceTypeStr())) {
					info.setPriceTypeStr(code.getItemValueByItemIdAndItemCode(Codes.CHARGE_TYPE_REFUND, priceType));
				}
				list.add(info);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	@Override
	public List<TransferFinanceInfo> findTransferFinanceDeail1(String dateFrom,String dateTo, int priceType) throws TmsDaoException {
		List<TransferFinanceInfo> list = new ArrayList<TransferFinanceInfo>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql1 = new StringBuffer();
		sql1.append(" select ");
		sql1.append(" A.serial serial ,");
		sql1.append(" A.attnId attnId,");
		sql1.append(" A.transferType transferType, ");
		sql1.append(" A.priceType priceType,");
		sql1.append(" A.price price,");
		sql1.append(" A.acceptedTime acceptedTime,");
		sql1.append(" A.priceUserId priceUserId,");
		sql1.append(" A.priceDate priceDate");
	    sql1.append(" FROM ");
		sql1.append(" (select ");
		sql1.append(" AA.serial ,");
		sql1.append(" BB.attnId ,");
		sql1.append(" BB.transferType, ");
		sql1.append(" AA.priceType ,");
		sql1.append(" AA.price ,");
		sql1.append(" AA.acceptedTime ,");
		sql1.append(" AA.priceUserId ,");
		sql1.append(" AA.priceDate ");
		sql1.append(" FROM ");
		sql1.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql1.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql1.append(" WHERE  AA.serial=BB.serial and BB.status in (0,16) and  AA.priceType= 9 ");
		sql1.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql1.append(" and   DATE_FORMAT(AA.acceptedTime,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql1.append("  union all ");
		sql1.append(" select ");
		sql1.append(" AA.serial ,");
		sql1.append(" BB.attnId ,");
		sql1.append(" BB.transferType, ");
		sql1.append(" AA.priceType ,");
		sql1.append(" AA.price ,");
		sql1.append(" AA.acceptedTime ,");
		sql1.append(" AA.priceUserId ,");
		sql1.append(" AA.priceDate ");
		sql1.append(" FROM ");
		sql1.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql1.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql1.append(" WHERE  AA.serial=BB.serial and  AA.priceType<> 9 ");
		sql1.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql1.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql1.append(" ) A  where A.priceType = "+priceType);
		sql1.append(" order by A.acceptedTime, A.serial  ");
		
		try {
			ps = conn.prepareStatement(sql1.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				TransferFinanceInfo info = new TransferFinanceInfo();
				info.setSerial(rs.getString("serial"));
				info.setAttnId(rs.getString("attnId"));
				info.setTransferType(rs.getString("transferType"));
				info.setPrice(rs.getString("price"));
				info.setPriceUserId(rs.getString("priceUserId"));
				info.setAcceptedTime(DateUtil.getTimeByTimeStamp(rs.getTimestamp("acceptedTime")));
				info.setPriceDate(DateUtil.getTimeByTimeStamp(rs.getTimestamp("priceDate")));
				info.setPriceType(rs.getInt("priceType"));
				list.add(info);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}
	
	@Override
	public List<TransferFinanceInfo> findTransferFinanceDeail0(String dateFrom,String dateTo) throws TmsDaoException {
		List<TransferFinanceInfo> list = new ArrayList<TransferFinanceInfo>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql0 = new StringBuffer();
		sql0.append(" select ");
		sql0.append(" AA.serial serial,");
		sql0.append(" BB.attnId attnId,");
		sql0.append(" BB.transferType transferType, ");
		sql0.append(" AA.priceType priceType,");
		sql0.append(" AA.price price,");
		sql0.append(" AA.acceptedTime acceptedTime,");
		sql0.append(" AA.priceUserId priceUserId,");
		sql0.append(" AA.priceDate priceDate");
		sql0.append(" FROM ");
		sql0.append(TmsTableNames.TMS_TRANSFER_FINANCE_DETAIL + " AA, ");
		sql0.append(TmsTableNames.TMS_TRANSFER_ACCEPTED_INFO + " BB ");
		sql0.append(" WHERE  AA.serial=BB.serial and  AA.priceType=9 and  BB.status not in (0,16) ");
		sql0.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') >= "+"'"+dateFrom+"'");
		sql0.append(" and   DATE_FORMAT(AA.priceDate,'%Y%m%d') <= "+"'"+dateTo+"'");
		sql0.append(" order by AA.acceptedTime, AA.serial  ");
		try {
			ps = conn.prepareStatement(sql0.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				TransferFinanceInfo info = new TransferFinanceInfo();
				info.setSerial(rs.getString("serial"));
				info.setAttnId(rs.getString("attnId"));
				info.setTransferType(rs.getString("transferType"));
				info.setPrice(rs.getString("price"));
				info.setPriceUserId(rs.getString("priceUserId"));
				info.setAcceptedTime(DateUtil.getTimeByTimeStamp(rs.getTimestamp("acceptedTime")));
				info.setPriceDate(DateUtil.getTimeByTimeStamp(rs.getTimestamp("priceDate")));
				info.setPriceType(rs.getInt("priceType"));
				list.add(info);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}
}
